package com.hw.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.afterturn.easypoi.util.PoiReflectorUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;

/**
 * 描述：Excel导入导出工具类
 *
 * @author whw
 * @date 2019/12/26 10:57
 */
@Slf4j
public class ExcelUtil extends ExcelExportService {
    /**
     * 当前所在行
     */
    private static int CURRENT_LINE = 0;

    /**
     * 模板导出
     *
     * @param templatePath 模板路径
     * @param fileName     导出的文件名
     * @param response     HttpServletResponse对象
     */
    public static void exportExcel(String templatePath, List<?> list, String fileName, HttpServletResponse response) {
        TemplateExportParams params = new TemplateExportParams(templatePath);
        Map<String, Object> map = new HashMap<>();
        map.put("list", list);
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        try {
            downloadExcel(fileName, workbook, response);
        } catch (IOException e) {
            log.error("Excel导出异常");
            e.printStackTrace();
        }
    }

    /**
     * Map集合导出
     *
     * @param list     需要导出的数据
     * @param fileName 导出的文件名
     * @param response HttpServletResponse对象
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    /**
     * 复杂导出Excel，包括文件名以及表名（不创建表头）
     *
     * @param list      需要导出的数据
     * @param title     表格首行标题（不需要就传null）
     * @param sheetName 工作表名称
     * @param pojoClass 映射的实体类
     * @param fileName  导出的文件名（如果为null，则默认文件名为当前时间戳）
     * @param response  HttpServletResponse对象
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 复杂导出Excel，包括文件名以及表名（创建表头）
     *
     * @param list           需要导出的数据
     * @param title          表格首行标题（不需要就传null）
     * @param sheetName      工作表名称
     * @param pojoClass      映射的实体类
     * @param fileName       导出的文件名
     * @param isCreateHeader 是否创建表头
     * @param response       HttpServletResponse对象
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName,
                                   boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 默认导出方法
     *
     * @param list         需要导出的数据
     * @param pojoClass    对应的实体类
     * @param fileName     导出的文件名
     * @param response     HttpServletResponse对象
     * @param exportParams 导出参数实体
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
                                      ExportParams exportParams) {
        try (Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list)) {
            downloadExcel(fileName, workbook, response);
        } catch (IOException e) {
            log.error("Excel导出异常");
            e.printStackTrace();
        }
    }

    /**
     * 默认导出方法
     *
     * @param list     Map集合
     * @param fileName 导出的文件名
     * @param response HttpServletResponse对象
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        try (Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF)) {
            downloadExcel(fileName, workbook, response);
        } catch (IOException e) {
            log.error("Excel导出异常");
            e.printStackTrace();
        }
    }

    /**
     * Excel导出
     *
     * @param fileName Excel导出
     * @param workbook Excel对象
     * @param response HttpServletResponse对象
     */
    public static void downloadExcel(String fileName, Workbook workbook, HttpServletResponse response) throws IOException {
        Assert.notNull(fileName, "导出文件名不能为空");
        String encodeFileName = URLEncoder.encode(fileName, "UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + encodeFileName);
        response.setHeader("FileName", encodeFileName);
        response.setHeader("Access-Control-Expose-Headers", "FileName");
        workbook.write(response.getOutputStream());
    }

    /**
     * 根据文件路径来导入Excel
     *
     * @param filePath   文件路径
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  映射的实体类
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        //判断文件是否存在
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setKeyIndex(0);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            log.error("模板不能为空", e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    /**
     * 根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @param file       上传的文件
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  映射的实体类
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setKeyIndex(0);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            log.error("excel文件不能为空", e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    /**
     * 根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @param file         上传的文件
     * @param importParams 导入参数，参考文档地址：http://doc.wupaas.com/docs/easypoi/easypoi-1c0u97casmdlh
     * @param pojoClass    映射的实体类
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, ImportParams importParams, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, importParams);
        } catch (NoSuchElementException e) {
            log.error("excel文件不能为空", e);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        return list;
    }

    /**
     * 根据接收的Excel文件来导入Excel,并封装成实体类 (校验必填字段,返回成功,失败结果)
     *
     * @param file       上传的文件
     * @param titleRows  表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass  映射的实体类
     * @return
     */
    public static <T> ExcelImportResult<T> importExcelByVerify(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass, IExcelVerifyHandler handler) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setKeyIndex(0);
        if (ObjectUtils.isNotEmpty(handler)) {
            params.setVerifyHandler(handler);
        }
        params.setNeedVerify(true);
        try {
            return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            log.error("excel文件不能为空", e);
            return null;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * @param field     当前操作字段
     * @param targetId  ExcelTarget注解中的ID值
     * @param pojoClass 映射的实体类
     * @return excel 导出工具类,对cell类型做映射
     */
    private ExcelExportEntity createExcelExportEntity(Field field, String targetId,
                                                      Class<?> pojoClass) {
        Excel excel = field.getAnnotation(Excel.class);
        ExcelExportEntity excelEntity = new ExcelExportEntity();
        excelEntity.setType(excel.type());
        getExcelField(targetId, field, excelEntity, excel, pojoClass);
        return excelEntity;
    }

    /**
     * 设置样式
     *
     * @param targetId    ExcelTarget注解中的ID值
     * @param field       当前操作字段
     * @param excelEntity excel 导出工具类,对cell类型做映射
     * @param excel       Excel注解对象
     * @param pojoClass   映射的实体类
     */
    private void getExcelField(String targetId, Field field, ExcelExportEntity excelEntity,
                               Excel excel, Class<?> pojoClass) {
        excelEntity.setName(PoiPublicUtil.getValueByTargetId(excel.name(), targetId, null));
        excelEntity.setWidth(excel.width());
        excelEntity.setHeight(excel.height());
        excelEntity.setNeedMerge(excel.needMerge());
        excelEntity.setMergeVertical(excel.mergeVertical());
        excelEntity.setMergeRely(excel.mergeRely());
        excelEntity.setReplace(excel.replace());
        excelEntity.setOrderNum(
                Integer.parseInt(PoiPublicUtil.getValueByTargetId(excel.orderNum(), targetId, "0")));
        excelEntity.setWrap(excel.isWrap());
        excelEntity.setExportImageType(excel.imageType());
        excelEntity.setSuffix(excel.suffix());
        excelEntity.setDatabaseFormat(excel.databaseFormat());
        excelEntity.setFormat(
                StringUtils.isNotEmpty(excel.exportFormat()) ? excel.exportFormat() : excel.format());
        excelEntity.setStatistics(excel.isStatistics());
        excelEntity.setHyperlink(excel.isHyperlink());
        excelEntity.setMethod(PoiReflectorUtil.fromCache(pojoClass).getGetMethod(field.getName()));
        excelEntity.setNumFormat(excel.numFormat());
        excelEntity.setColumnHidden(excel.isColumnHidden());
        excelEntity.setDict(excel.dict());
        excelEntity.setEnumExportField(excel.enumExportField());
        excelEntity.setTimezone(excel.timezone());
        excelEntity.setGroupName(excel.groupName());
        if (i18nHandler != null) {
            excelEntity.setName(i18nHandler.getLocaleName(excelEntity.getName()));
            excelEntity.setGroupName(i18nHandler.getLocaleName(excelEntity.getGroupName()));
        }
    }

    /**
     * 文件转List
     *
     * @param file
     * @param pojoClass
     * @param <T>
     * @return
     */
    public static <T> List<T> fileToList(MultipartFile file, Class<T> pojoClass) {
        if (ObjectUtils.isEmpty(file)) {
            throw new RuntimeException("文件为空");
        }
        List<T> list = importExcel(file, 1, 1, pojoClass);
        if (CollectionUtils.isEmpty(list)) {
            throw new RuntimeException("未解析到表格数据");
        }
        return list;
    }

    /**
     * 文件转list校验
     *
     * @param file
     * @param pojoClass
     * @param <T>
     * @return
     */
    public static <T> ExcelImportResult<T> fileToListByVerify(MultipartFile file, Class<T> pojoClass, IExcelVerifyHandler handler) {
        if (ObjectUtils.isEmpty(file)) {
            throw new RuntimeException("文件为空");
        }
        return importExcelByVerify(file, 1, 1, pojoClass, handler);
    }

    /**
     * 获取obj对应的.class
     *
     * @param obj       需要判断的对象
     * @param pojoClass 用来比较的对象集合
     * @return 返回对象对应的.class
     */
    public static Class<?> getObjClass(Object obj, List<Class<?>> pojoClass) {
        AtomicReference<Class<?>> type = new AtomicReference<>();
        pojoClass.forEach(data -> {
            if (data.isInstance(obj)) {
                type.set(data);
            }
        });
        return type.get();
    }
}
